package use;
import swimming.VIPcard;
import swimming.DBUtils;

import javax.swing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class VIPuse {
    public List<VIPcard> 搜索all() throws SQLException{
            List<VIPcard> list = new ArrayList<>();
            Connection con = DBUtils.getConnection();
            PreparedStatement st = con.prepareStatement("select  * from 会员");
            ResultSet re = st.executeQuery();
            list = result(con, st, re);
            return list;
        }
    public List<VIPcard> 搜索(String input) throws SQLException{
        List<VIPcard> list=new ArrayList<>();
        Connection con=DBUtils.getConnection();
        PreparedStatement st= con.prepareStatement("select  * from 会员 where 电话=? or 姓名=? or 状态=? or 类型=? or 金额折扣=?");
        st.setString(1,input);
        st.setString(2,input);
        st.setString(3,input);
        st.setString(4,input);
        st.setString(5,input);
        ResultSet re=st.executeQuery();
        list=result(con,st,re);
        return list;
    }
    public List<? extends Object> 增加(String []input)
    {
        try {
            List<VIPcard> list = new ArrayList<>();
            Connection con = DBUtils.getConnection();
            PreparedStatement st = con.prepareStatement("insert into 会员(电话,姓名,状态,类型,金额折扣,备注) value(?,?,?,?,?,?)");
            for (int i = 0; i < input.length; i++) {
                st.setString(i + 1, input[i]);
            }
            st.executeUpdate();
            st = con.prepareStatement("select  * from 会员");
            ResultSet re = st.executeQuery();
            list = result(con, st, re);
            return list;
        }catch (SQLException E) {
            return null;
        }
        }
    public List<? extends Object> 删除(String input)
    {
        try{
        List<VIPcard> list=new ArrayList<>();
        Connection con=DBUtils.getConnection();
        PreparedStatement st= con.prepareStatement("delete from 会员 where 电话=?");
        st.setString(1,input);
        st.executeUpdate();
        st= con.prepareStatement("select  * from 会员");
        ResultSet re=st.executeQuery();
        list=result(con,st,re);
        return list;}
        catch (SQLException E) {
        return null;
    }
    }
    public List<? extends Object> 修改姓名(String []input)
    {
        try{
        List<VIPcard> list=new ArrayList<>();
        Connection con=DBUtils.getConnection();
        PreparedStatement st= con.prepareStatement("update 会员 set 姓名=? where 电话=?");
        st.setString(1,input[0]);
        st.setString(2,input[1]);
        st.executeUpdate();
        st= con.prepareStatement("select  * from 会员");
        ResultSet re=st.executeQuery();
        list=result(con,st,re);
        return list;}
        catch (SQLException E) {
            return null;
        }
    }
    public List<? extends Object> 修改为禁用状态(String []input)
    { try{
        List<VIPcard> list=new ArrayList<>();
        Connection con=DBUtils.getConnection();
        PreparedStatement st= con.prepareStatement("update 会员 set 状态=?,备注=? where 电话=?");
        st.setString(1,input[0]);
        st.setString(2,input[1]);
        st.setString(3,input[2]);
        st.executeUpdate();
        st= con.prepareStatement("select  * from 会员");
        ResultSet re=st.executeQuery();
        list=result(con,st,re);
        return list;}
        catch (SQLException E) {
        return null;
    }
    }
    public List<? extends Object> 修改为正常状态(String []input)
    {
        try{
        List<VIPcard> list=new ArrayList<>();
        Connection con=DBUtils.getConnection();
        PreparedStatement st= con.prepareStatement("update 会员 set 状态=?,备注=null where 电话=?");
        st.setString(1,input[0]);
        st.setString(2,input[1]);
        st.executeUpdate();
        st= con.prepareStatement("select  * from 会员");
        ResultSet re=st.executeQuery();
        list=result(con,st,re);
        return list;}
        catch (SQLException E) {
            return null;
        }
    }
    public List<? extends Object> 修改电话(String[] input)
    {try{
        List<VIPcard> list=new ArrayList<>();
        Connection con=DBUtils.getConnection();
        PreparedStatement st= con.prepareStatement("update 会员 set 电话=? where 电话=?");
        st.setString(1,input[0]);
        st.setString(2,input[1]);
        st.executeUpdate();
        st= con.prepareStatement("select  * from 会员");
        ResultSet re=st.executeQuery();
        list=result(con,st,re);
        return list;}
    catch (SQLException E) {
        return null;
    }
    }
    public List<? extends Object> 修改money(String[] input)
    {try{
        List<VIPcard> list=new ArrayList<>();
        Connection con=DBUtils.getConnection();
        PreparedStatement st= con.prepareStatement("update 会员 set 金额折扣=?+金额折扣 where 电话=?");
        st.setString(1,input[0]);
        st.setString(2,input[1]);
        st.executeUpdate();
        st= con.prepareStatement("select  * from 会员 where 电话=?");
        st.setString(1,input[1]);
        ResultSet re=st.executeQuery();
        list=result(con,st,re);
        return list;}
    catch (SQLException E) {
        return null;
    }
    }
    public List<? extends Object> 使用vip(String[] input)
    {try{
        List<VIPcard> list=new ArrayList<>();
        Connection con=DBUtils.getConnection();
        PreparedStatement st= con.prepareStatement("update 会员 set 金额折扣=? where 电话=?");
        st.setString(1,input[0]);
        st.setString(2,input[1]);
        st.executeUpdate();
        st= con.prepareStatement("select  * from 会员 where 电话=?");
        st.setString(1,input[1]);
        ResultSet re=st.executeQuery();
        list=result(con,st,re);
        return list;}
    catch (SQLException E) {
        return null;
    }
    }
    public List<VIPcard> result(Connection con,PreparedStatement st,ResultSet re) throws SQLException
    {
        List<VIPcard> list=new ArrayList<>();
        try{
            while(re.next())
            {
                String cardnum=re.getString("电话");
                String name=re.getString("姓名");
                String state=re.getString("状态");
                String cardtyoe=re.getString("类型");
                String money=re.getString("金额折扣");
                String why=re.getString("备注");
                VIPcard vipc=new VIPcard(cardnum,name,state,cardtyoe,money,why);
                list.add(vipc);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            DBUtils.close(con,st,re);
        }
        return list;
    }
}
